package tcm.com.gistone.api.restController;

import net.sf.json.JSONObject;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import tcm.com.gistone.api.service.KnowledgeService;
import tcm.com.gistone.api.service.WordService;
import tcm.com.gistone.database.config.GetBySqlMapper;
import tcm.com.gistone.database.entity.Directory;
import tcm.com.gistone.database.entity.Section;
import tcm.com.gistone.database.mapper.DirectoryMapper;
import tcm.com.gistone.database.mapper.SectionMapper;
import tcm.com.gistone.util.ClientUtil;
import tcm.com.gistone.util.ConfigUtil;
import tcm.com.gistone.util.EdatResult;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by wangfan on 2017/8/12.
 */
@RestController("apiKnowledgeController")
@RequestMapping("api/knowledge")
public class KnowledgeController {


    @Autowired
    private GetBySqlMapper getBySqlMapper;
    @Autowired
    WordService wordService;

    @Autowired
    KnowledgeService knowledgeService;

    @Autowired
    private ConfigUtil configUtil;
    /**
     * 获取专家信息列表
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "getKnowledgeInfoList", method = RequestMethod.POST)
    public EdatResult getKnowledgeInfoList(HttpServletRequest request, HttpServletResponse response) {
        try {
            ClientUtil.SetCharsetAndHeader(request, response);
            JSONObject data = JSONObject.fromObject(request.getParameter("data"));
            int bookId = data.getInt("bookId");
            String word = data.getString("word");

            String sql = "select b.book_name,a.* from tb_knowledge a,tb_bookinfo b  where a.book_id =b.book_id and  b.status=7 and a.book_id=" + bookId + " and a.knowledge_name ='" + word + "'";
            List<Map> result = getBySqlMapper.findRecords(sql);

           /* Map<String,Object> info = result.get(0);

            for (Map.Entry<String,Object> entry : info.entrySet()) {
                if(null!=entry.getValue()){



                }




            }*/

           List<Map> maps = new ArrayList<>();

            if (result.size() > 0) {

                Map<String, Object>  map =result.get(0);

                for (Map.Entry<String, Object> entry : map.entrySet()) {
                    Map r = new HashMap();
                    if(!entry.getKey().equals("special_id")&&!entry.getKey().equals("book_id")&&!entry.getKey().equals("knowledge_id")&&!entry.getKey().equals("knowledge_name")&&null!=entry.getValue()&&!"".equals(entry.getValue())){
                        r.put("key",entry.getKey());
                        r.put("value",entry.getValue());
                        maps.add(r);
                    }


                }
                return EdatResult.ok(maps);
            } else {
                return EdatResult.build(2, "no knowledge");
            }

        } catch (Exception e) {
            e.printStackTrace();
            return EdatResult.build(1, "fail");
        }
    }




    @RequestMapping(value = "getWBKnowledgeInfoList", method = RequestMethod.POST)
    public EdatResult getWBKnowledgeInfoList(HttpServletRequest request, HttpServletResponse response) {
        try {
            ClientUtil.SetCharsetAndHeader(request, response);
            JSONObject data = JSONObject.fromObject(request.getParameter("data"));
            String word = data.getString("word");

            String sql = "select * from tb_warm_knowledge where  word ='" + word + "'";
            List<Map> result = getBySqlMapper.findRecords(sql);


            List<Map> maps = new ArrayList<>();

            if (result.size() > 0) {

                Map<String, Object>  map =result.get(0);

                for (Map.Entry<String, Object> entry : map.entrySet()) {
                    Map r = new HashMap();
                    if(!entry.getKey().equals("id")&&!entry.getKey().equals("word")&&null!=entry.getValue()&&!"".equals(entry.getValue())){
                        r.put("key",entry.getKey());
                        r.put("value",entry.getValue()==null?" ":entry.getValue());
                        maps.add(r);
                    }


                }
                return EdatResult.ok(maps);
            } else {
                return EdatResult.build(2, "no knowledge");
            }

        } catch (Exception e) {
            e.printStackTrace();
            return EdatResult.build(1, "fail");
        }
    }

    /**
     * 获取有关键词对应的专家知识书籍列表，不包含自己
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "getCompareBookList", method = RequestMethod.POST)
    public EdatResult getCompareBookList(HttpServletRequest request, HttpServletResponse response) {
        try {
            ClientUtil.SetCharsetAndHeader(request, response);
            JSONObject data = JSONObject.fromObject(request.getParameter("data"));
            int bookId = data.getInt("bookId");
            String word = data.getString("word");

            String sql = "select b.book_name,b.book_id from tb_knowledge a,tb_bookinfo b  where a.book_id =b.book_id and b.status=7 and  a.book_id !=" + bookId + " and a.knowledge_name ='" + word + "'";
            List<Map> result = getBySqlMapper.findRecords(sql);



            return EdatResult.ok(result);
        } catch (Exception e) {
            e.printStackTrace();
            return EdatResult.build(1, "fail");
        }
    }


    /**
     * 获取比对结果集
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "getKnowledgeBookList", method = RequestMethod.POST)
    public EdatResult getKnowledgeBookList(HttpServletRequest request, HttpServletResponse response) {
        try {
            ClientUtil.SetCharsetAndHeader(request, response);
            JSONObject data = JSONObject.fromObject(request.getParameter("data"));
            int bookId1 = data.getInt("bookId1");
            int bookId2 = data.getInt("bookId2");
            String word = data.getString("word");
            List<Map> result =new ArrayList<>();
            result.add( knowledgeService.getKnowledgeInfo(bookId1,word));
            result.add( knowledgeService.getKnowledgeInfo(bookId2,word));
            return EdatResult.ok(result);
        } catch (Exception e) {
            e.printStackTrace();
            return EdatResult.build(1, "fail");
        }
    }



    /**
     * 温病专题知识
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "getWbKnowledgeInfo", method = RequestMethod.POST)
    public EdatResult getWbKnowledgeInfo(HttpServletRequest request, HttpServletResponse response) {
        try {
            ClientUtil.SetCharsetAndHeader(request, response);
            JSONObject data = JSONObject.fromObject(request.getParameter("data"));
            Integer specialId = data.getInt("specialId");
            if(specialId!=3){
                return EdatResult.build(1, "fail");
            }
            String word = data.getString("word");

            String sql = "select a.*,b.pic_url from tb_warm_knowledge a ,tb_word b  where a.word =b.word and b.special_id=3 and a.word ='" + word + "'";
            List<Map> result = getBySqlMapper.findRecords(sql);


            if (result.size() > 0) {
                return EdatResult.ok(result.get(0));
            }
            return EdatResult.build(2, "no knowledge","");

        } catch (Exception e) {
            e.printStackTrace();
            return EdatResult.build(1, "fail");
        }
    }


    /**
     * 本草专题知识
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "getKnowledgeInfo", method = RequestMethod.POST)
    public EdatResult getKnowledgeInfo(HttpServletRequest request, HttpServletResponse response) {
        try {
            ClientUtil.SetCharsetAndHeader(request, response);
            JSONObject data = JSONObject.fromObject(request.getParameter("data"));
            int bookId = data.getInt("bookId");
            String word = data.getString("word");

            String sql = "select b.book_name,c.pic_url,a.* from tb_knowledge a,tb_bookinfo b,tb_word c  where a.book_id =b.book_id and a.knowledge_name=c.word and c.special_id=1  and b.status=7 and  a.book_id=" + bookId + " and a.knowledge_name ='" + word + "'";


            List<Map> result = getBySqlMapper.findRecords(sql);

           /* Map<String,Object> info = result.get(0);

            for (Map.Entry<String,Object> entry : info.entrySet()) {
                if(null!=entry.getValue()){



                }





            }*/

            if (result.size() > 0) {
                return EdatResult.ok(result.get(0));
            } else {
                String sql2 = "select * from tb_bookinfo   where status=7 and book_id=" + bookId ;
                List<Map> result2= getBySqlMapper.findRecords(sql2);
                return EdatResult.build(2, "no knowledge",result2.get(0));
            }

        } catch (Exception e) {
            e.printStackTrace();
            return EdatResult.build(1, "fail");
        }
    }


    /**
     * 获取二级拓扑图相关信息
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "getRelation", method = RequestMethod.POST)
    public EdatResult getRelation(HttpServletRequest request, HttpServletResponse response) {
        try {
            ClientUtil.SetCharsetAndHeader(request, response);
            JSONObject data = JSONObject.fromObject(request.getParameter("data"));
            String word = data.getString("word");
            String dataColumn = data.getString("dataColumn");
            String dataValue = data.getString("dataValue");
            String sql ="";
            if(dataColumn.equals("book_name")){

                 sql =

                        "SELECT  knowledge_name  FROM tb_knowledge,tb_bookinfo WHERE tb_knowledge.book_id=tb_bookinfo.book_id and  knowledge_name !='" + word + "' and tb_bookinfo.book_name ='" + dataValue + "' and tb_bookinfo.special_id =1";


            }else{
                 sql =

                        "SELECT  knowledge_name  FROM tb_knowledge WHERE knowledge_name !='" + word + "' and "+dataColumn+" ='" + dataValue + "' and special_id =1";


            }
            List<Map> result = getBySqlMapper.findRecords(sql);

            return EdatResult.ok(result);

        } catch (Exception e) {
            e.printStackTrace();
            return EdatResult.build(1, "fail");
        }
    }


    @RequestMapping(value = "downloadExcelFile/{filename:.+}", method = RequestMethod.POST)
    public void downloadExcelFile(@PathVariable("filename") String filename, HttpServletResponse response) throws Exception {

        // create full filename and get input stream
        File file = new File (configUtil.getUrl()+"Excel/" + filename);
        InputStream is = new FileInputStream(file);

        response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8"));
        FileCopyUtils.copy(is, response.getOutputStream());

        file.delete();
        // close stream and return to view
        response.flushBuffer();
    }

    @RequestMapping("/generateExcel")
    public EdatResult generateExcel(HttpServletRequest request, HttpServletResponse response)
    {  //我这是根据前端传来的起始时间来查询数据库里的数据，如果没有输入变量要求，保留前两个就行

        List<Map> allResults=null;
        try {
            ClientUtil.SetCharsetAndHeader(request, response);
            JSONObject data = JSONObject.fromObject(request.getParameter("data"));
            System.out.println(data.toString());
            Integer specialId = null;
            if (data.has("specialId")) {
                specialId = data.getInt("specialId");

            }
            String word = data.getString("word");
            long queryWordId = wordService.getWordIdByWordString(specialId, word);
            allResults =new ArrayList<>();

            if (queryWordId > 0) {

                List<Map> bookList = wordService.getStatisticsByWord(specialId, queryWordId);

                if (bookList.size() > 0) {

                    for (int i = 0; i < bookList.size(); i++) {
                        long bookId=(long)bookList.get(i).get("book_id");
                        Map m=bookList.get(i);
                        List<Map> sectionList =wordService.getListByWordAndBookId(word,bookId);
                        m.put("sectionList",sectionList);
                        allResults.add(m);
                    }



                }


            }




            String[] headers = { "ID","标题","内容"};//导出的Excel头部，这个要根据自己项目改一下

            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet();
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth((short) 18);
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
            }

            int index = 0;
            for (int i = 0; i < allResults.size(); i++) {
                Map t =allResults.get(i);
                List<Map> sectionList = (List<Map>) t.get("sectionList");
                index++;
                row = sheet.createRow(index);//生成行
                // 开始行，结束行，开始列，结束列
                sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 2));
                HSSFCell cell = row.createCell(0);
                HSSFRichTextString richString = new HSSFRichTextString(t.get("book_name").toString()+"("+sectionList.size()+")");
                HSSFFont font = workbook.createFont();
                font.setColor(HSSFColor.BLUE.index);//定义Excel数据颜色
                richString.applyFont(font);
                cell.setCellValue(richString);

                for (int j = 0; j < sectionList.size(); j++) {
                    index++;
                    t =sectionList.get(j);
                    row = sheet.createRow(index);//生成行
                    cell = row.createCell(0);
                    richString = new HSSFRichTextString(t.get("sectionId").toString());
                    font = workbook.createFont();
                    font.setColor(HSSFColor.BLACK.index);//定义Excel数据颜色
                    richString.applyFont(font);
                    cell.setCellValue(richString);

                    cell = row.createCell(1);
                    richString = new HSSFRichTextString(t.get("sectionTitle").toString());
                    font = workbook.createFont();
                    font.setColor(HSSFColor.BLACK.index);//定义Excel数据颜色
                    richString.applyFont(font);
                    cell.setCellValue(richString);

                    cell = row.createCell(2);
                    richString = new HSSFRichTextString(t.get("sectionContent").toString());
                    font = workbook.createFont();
                    font.setColor(HSSFColor.BLACK.index);//定义Excel数据颜色
                    richString.applyFont(font);
                    cell.setCellValue(richString);


                }



            }
          /*  //遍历集合数据，产生数据行
            Iterator<Map> it = allResults.iterator();
            int index = 0;
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                Map t =  it.next();
                HSSFCell cell = row.createCell(0);
                HSSFRichTextString richString = new HSSFRichTextString(t.get("book_name").toString());
                HSSFFont font3 = workbook.createFont();
                font3.setColor(HSSFColor.BLUE.index);//定义Excel数据颜色
                richString.applyFont(font3);
                cell.setCellValue(richString);


            }*/
//            response.setContentType("application/octet-stream");
//            response.setHeader("Content-disposition", "attachment;filename=createList.xls");//默认Excel名称
//            response.flushBuffer();
//            workbook.write(response.getOutputStream());


            File f =new File(configUtil.getUrl()+"Excel");
            if(!f.isDirectory()){
                f.mkdirs();

            }

            String filepath =configUtil.getUrl()+"Excel"+"/"+word+".xls";
            f= new File(filepath);
            if(f.exists()){
                f.delete();
            }
            FileOutputStream outputStream = new FileOutputStream(filepath);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
//            workbook.write(outputStream.getOutputStream());

            return EdatResult.build(0, "success",word+".xls");
        } catch (Exception e) {
            e.printStackTrace();
            return EdatResult.build(1, "fail");
        }
    }
}
